// 使用 node24 内置的 sqlite 模块实现数据库访问
import { DatabaseSync } from 'node:sqlite';

// 连接数据库
const db = new DatabaseSync('./demo.db');
const sql = db.createTagStore();

// 执行建表语句
db.exec(`
  CREATE TABLE IF NOT EXISTS Users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username VARCHAR(32) NOT NULL,
    password VARCHAR(128) NOT NULL,
    email VARCHAR(128),
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);`)
const username = '李四'
const password = '123456'
const email = '7989777@qq.com'
// 执行插入语句
const rs1 = sql.run`INSERT INTO Users (username, password, email) VALUES (${username}, ${password}, ${email})`
console.log(rs1);
// 执行查询语句
// 查询单条数据
const rs2 = sql.get`SELECT * FROM Users WHERE id = ${rs1.lastInsertRowid}`
console.log(rs2);
// 查询多条数据
const rs3 = sql.all`SELECT * FROM Users`
console.log(rs3);
// 执行更新语句
const rs4 = sql.run`UPDATE Users SET username = '王五' WHERE id = ${rs1.lastInsertRowid}`
console.log(rs4);
// 执行删除语句
const rs5 = sql.run`DELETE FROM Users WHERE id = ${rs1.lastInsertRowid}`
console.log(rs5);
// 关闭数据库
db.close();